﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;

namespace Helper
{
    public sealed class ExcelHelper : INotifyPropertyChanged, IDisposable
    {
        bool disposed = false;
        public event PropertyChangedEventHandler PropertyChanged;
        private void NotifyPropertyChanged(String info)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(info));
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        private void Dispose(bool disposing)
        {
            if (disposed)
                return;

            if (disposing)
            { 
            }
            ReleaseObjects();
            disposed = true;

        }
        
        ~ExcelHelper()
        {
            Dispose(false);
        }

        private Excel.Application xlAppObj = null;

        private Excel.Workbook xlWorkBookObj = null;

        private Excel.Worksheet xlWorkSheetObj = null;

        private string s_filePath = null;

        private string s_workSheetName = null;

        private int n_workSheetNo = 0;

        private List<int> l_initialProcessIds = null;

        private List<int> l_finalProcessIds = null;

        private int n_processId = 0;
        private bool isAppClosed = false;


        public bool IsAppClosed
        {
            get { return this.isAppClosed; }

            set
            {
                if (value != this.isAppClosed)
                {
                    this.isAppClosed = value;
                    NotifyPropertyChanged("IsAppClosed");
                }
            }
        }
        public int NumberOfWorksheets
        {
            get { return n_workSheetNo; }
        }

        public ExcelHelper(string filePath)
        {
            s_filePath = filePath;
            n_workSheetNo = 1;              // "1" Default WorkSheetNo
        }
        
        public ExcelHelper()
        {
            xlAppObj = new Excel.Application();
            xlWorkBookObj = xlAppObj.Workbooks.Add(System.Reflection.Missing.Value);
            xlWorkSheetObj = (Excel.Worksheet)xlWorkBookObj.Worksheets.Add(Type.Missing, Type.Missing,
                                           Type.Missing, Type.Missing);
        }
        
        public ExcelHelper(string filePath, int workSheetNo)
        {
            s_filePath = filePath;
            n_workSheetNo = workSheetNo;
        }
        public int TotalNoOfColumns
        {
            get
            {
                int NumberOfColumns = 0;
                try
                {
                    NumberOfColumns = xlWorkSheetObj.Cells.Find("*", Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns,
                        Excel.XlSearchDirection.xlPrevious, false, false, Type.Missing).Column;
                    return NumberOfColumns;
                }
                catch
                {
                    return NumberOfColumns;
                }
            }
        }
        public int TotalNoOfRows
        {
            get
            {
                int NumberOfRows = 0;
                try
                {
                    NumberOfRows = xlWorkSheetObj.Cells.Find("*", Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns,
                       Excel.XlSearchDirection.xlPrevious, false, false, Type.Missing).Row;
                    return NumberOfRows;
                }
                catch
                {
                    return NumberOfRows;
                }
            }
        }

        public ExcelHelper(string filePath, string workSheetName)
        {
            s_filePath = filePath;
            s_workSheetName = workSheetName;
        }


        public Excel.Worksheet XlWorkSheetObj
        {
            get { return xlWorkSheetObj; }
        }
        public Excel.Workbook XlWorkBookObj
        {
            get { return xlWorkBookObj; }
        }
        public Excel.Application XlAppObj
        {
            get { return xlAppObj; }
        }
        public bool SaveWorkBook(string filePath)
        {
            try
            {
                object misValue = System.Reflection.Missing.Value;
                xlWorkBookObj.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue,
                    false, false, Excel.XlSaveAsAccessMode.xlNoChange, misValue, false, misValue, misValue, misValue);
                return true;
            }
            catch { return false; }
        }
       
        public bool Open()
        {
            try
            {
                l_initialProcessIds = this.GetExcelProcessIdsSnapshot();
                xlAppObj = new Excel.ApplicationClass();
                xlWorkBookObj = xlAppObj.Workbooks.Open(s_filePath, 0, false, 5, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);

                if (n_workSheetNo != 0 && s_workSheetName == null)
                {
                    xlWorkSheetObj = (Excel.Worksheet)xlWorkBookObj.Worksheets.get_Item(n_workSheetNo);

                    l_finalProcessIds = this.GetExcelProcessIdsSnapshot();
                    n_processId = this.GetExcelProcessId(l_initialProcessIds, l_finalProcessIds);
                }

                if (s_workSheetName != null && n_workSheetNo == 0)
                {
                    bool b_worksheetNameExist = true;
                    for (int n_loop = 1; n_loop <= xlWorkBookObj.Worksheets.Count; n_loop++)
                    {
                        xlWorkSheetObj = (Excel.Worksheet)xlWorkBookObj.Worksheets.get_Item(n_loop);
                        if (xlWorkSheetObj.Name == s_workSheetName)
                        {
                            b_worksheetNameExist = true;
                            break;
                        }
                        b_worksheetNameExist = false;
                    }

                    if (b_worksheetNameExist == true)
                    {
                        l_finalProcessIds = this.GetExcelProcessIdsSnapshot();
                        n_processId = this.GetExcelProcessId(l_initialProcessIds, l_finalProcessIds);
                    }
                    else
                    {
                        // Exception is on its way........ ting
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public void Close()
        {
            try
            {
                IsAppClosed = true;
                if (xlWorkBookObj != null)
                {
                    xlWorkBookObj.Save();
                    xlWorkBookObj.Close(true, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                }
            }
            catch
            {

            }
        }

        public bool UseWorkSheet(int workSheetNo)
        {
            try
            {
                xlWorkSheetObj = (Excel.Worksheet)xlWorkBookObj.Worksheets.get_Item(workSheetNo);
                return true;
            }
            catch
            {
                return false;
            }
        }

        #region ReleaseObjects

      
        private void ReleaseWorkSheetObject()
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(this.xlWorkSheetObj);
                this.xlWorkSheetObj = null;
            }
            catch
            {

            }
        }

        private void ReleaseWorkBookObject()
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(this.xlWorkBookObj);
                this.xlWorkBookObj = null;
            }
            catch
            {

            }
        }
        
        private void ReleaseApplicationObject()
        {
            try
            {
                xlAppObj.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(this.xlAppObj);
                this.xlAppObj = null;
            }
            catch
            {

            }
        }

        private void ReleaseObjects()
        {
            if (xlWorkSheetObj != null)
                this.ReleaseWorkSheetObject();

            if (xlWorkBookObj != null)
                this.ReleaseWorkBookObject();

            if (xlAppObj != null)
                this.ReleaseApplicationObject();

            if (n_processId != 0)
                this.KillExcelProcessById(n_processId);
        }

        #endregion

        #region Kill Processes

        private List<int> GetExcelProcessIdsSnapshot()
        {
            List<int> ProcessIds = new List<int>();

            try
            {
                Process[] Processes = Process.GetProcessesByName("Excel");

                for (int n_loop = 0; n_loop < Processes.Count(); n_loop++)
                    ProcessIds.Add(Processes.ElementAt(n_loop).Id);
            }
            catch
            {

            }

            return ProcessIds;
        }

        private int GetExcelProcessId(List<int> l_initialProcessIds, List<int> l_finalProcessIds)
        {
            try
            {
                for (int n_loop = 0; n_loop < l_initialProcessIds.Count; n_loop++)
                {
                    int n_PidInitialProcessList = l_initialProcessIds.ElementAt(n_loop);

                    for (int n_innerloop = 0; n_innerloop < l_finalProcessIds.Count; n_innerloop++)
                    {
                        int n_PidFinalProcessList = l_finalProcessIds.ElementAt(n_innerloop);
                        if (n_PidInitialProcessList == n_PidFinalProcessList)
                        {
                            l_finalProcessIds.RemoveAt(n_innerloop);
                            break;
                        }
                    }
                    l_initialProcessIds.RemoveAt(n_loop);
                    n_loop--;
                }
            }
            catch
            {

            }

            return l_finalProcessIds.ElementAt(0);
        }

        private void KillExcelProcessById(int n_processId)
        {
            try
            {
                Process xlProcess = null;
                xlProcess = Process.GetProcessById(n_processId);
                xlProcess.Kill();
            }
            catch
            {
            }
        }
        #endregion

        # region Read Operations

        public int ReadFromCellInteger(int rowId, int colId)
        {
            int cellValue = 0;
            try
            {
                cellValue = Convert.ToInt32(this.ReadFromCell(rowId, colId));
            }
            catch
            {

            }

            return cellValue;
        }

        public int ReadFromCellInteger(object indexLoc)
        {
            int cellValue = 0;
            try
            {
                cellValue = Convert.ToInt32(this.ReadFromCell(indexLoc));
            }
            catch
            {

            }

            return cellValue;
        }

        public double ReadFromCellDouble(int rowId, int colId)
        {
            double cellValue = 0;
            try
            {
                cellValue = Convert.ToDouble(this.ReadFromCell(rowId, colId));
            }
            catch
            {

            }

            return cellValue;
        }

        public double ReadFromCellDouble(object indexLoc)
        {
            double cellValue = 0;
            try
            {
                cellValue = Convert.ToDouble(this.ReadFromCell(indexLoc));
            }
            catch
            {

            }

            return cellValue;
        }

        public string ReadFromCellString(int rowId, int colId)
        {
            string cellValue = null;
            try
            {
                cellValue = Convert.ToString(this.ReadFromCell(rowId, colId));
            }
            catch
            {

            }

            return cellValue;
        }

        public string ReadFromCellString(object indexLoc)
        {
            string cellValue = null;
            try
            {
                cellValue = Convert.ToString(this.ReadFromCell(indexLoc));
            }
            catch
            {

            }

            return cellValue;
        }

        public DateTime ReadFromCellDateTime(int rowId, int colId)
        {
            DateTime cellValue = DateTime.MinValue;
            try
            {
                cellValue = Convert.ToDateTime(this.ReadFromCell(rowId, colId));
            }
            catch
            {

            }

            return cellValue;
        }

        public DateTime ReadFromCellDateTime(object indexLoc)
        {
            DateTime cellValue = DateTime.MinValue;
            try
            {
                cellValue = Convert.ToDateTime(this.ReadFromCell(indexLoc));
            }
            catch
            {

            }

            return cellValue;
        }

        private object ReadFromCell(int rowId, int colId)
        {
            object cellValue = null;
            Excel.Range range = null;

            range = (Excel.Range)xlWorkSheetObj.UsedRange;
            cellValue = (object)(range.Cells[rowId, colId] as Excel.Range).Value2;

            return cellValue;
        }

        private object ReadFromCell(object indexLoc)
        {
            object cellValue = null;
            Excel.Range range = null;

            range = xlWorkSheetObj.get_Range(indexLoc, indexLoc);
            cellValue = range.Value2;

            return cellValue;
        }

        public object[,] ReadFromCells(object startIndexLoc, object endIndexLoc)
        {
            object[,] cellValues = null;
            Excel.Range range = null;
            try
            {
                range = xlWorkSheetObj.get_Range(startIndexLoc, endIndexLoc);
                cellValues = (object[,])range.Value2;
            }
            catch
            {
            }

            return cellValues;
        }

        #endregion

        # region Write Operations

        public void WriteToCell(int rowId, int colId, int cellValue)
        {
            try
            {
                xlWorkSheetObj.Cells[rowId, colId] = cellValue;
            }
            catch
            {

            }
        }

        public void WriteToCell(int rowId, int colId, double cellValue)
        {
            try
            {
                xlWorkSheetObj.Cells[rowId, colId] = cellValue;
            }
            catch
            {

            }
        }

        public void WriteToCell(int rowId, int colId, string cellValue)
        {
            try
            {
                xlWorkSheetObj.Cells[rowId, colId] = cellValue;
            }
            catch
            {

            }
        }

        public void WriteToCell(int rowId, int colId, DateTime cellValue)
        {
            try
            {
                xlWorkSheetObj.Cells[rowId, colId] = cellValue;
            }
            catch
            {

            }
        }

        public void WriteToCells(object startIndexLoc, object endIndexLoc, int[,] cellValues)
        {
            Excel.Range range = null;

            try
            {
                range = xlWorkSheetObj.get_Range(startIndexLoc, endIndexLoc);
                range.Value = cellValues;
            }
            catch
            {

            }
        }

        public void WriteToCells(object startIndexLoc, object endIndexLoc, double[,] cellValues)
        {
            Excel.Range range = null;

            try
            {
                range = xlWorkSheetObj.get_Range(startIndexLoc, endIndexLoc);
                range.Value = cellValues;
            }
            catch
            {

            }
        }

        public void WriteToCells(object startIndexLoc, object endIndexLoc, string[,] cellValues)
        {
            Excel.Range range = null;

            try
            {
                range = xlWorkSheetObj.get_Range(startIndexLoc, endIndexLoc);
                range.Value = cellValues;
            }
            catch
            {

            }
        }

        public void WriteToCells(object startIndexLoc, object endIndexLoc, DateTime[,] cellValues)
        {
            Excel.Range range = null;

            try
            {
                range = xlWorkSheetObj.get_Range(startIndexLoc, endIndexLoc);
                range.Value = cellValues;
            }
            catch
            {

            }
        }

        #endregion

        public ExcelHelper LoadTemplate(List<string> columns)
        {
            try
            {
                Set_BG_Font_ColWidth_Comment(columns);
                WriteTemplateHeadings(columns);
                SaveWorkBook(GetNewTempPath());
                XlAppObj.Visible = true;
            }
            catch
            {
            }
            return this;
        }

        private static string GetColumnNameFromIndex(int colId)
        {
            string s_colValue = null;

            try
            {
                // This logic would only work for only for Single Character Alphabets
                //if (colId >= 1 && colId <= 26)
                //{
                //    s_colValue = ((char)((colId + 65) - 1)).ToString();
                //}

                // This logic would only work for only Double Character Alphabets 
                //if (colId >= 27 && colId <= 256)
                //{
                //    s_colValue = ((char)(((colId - 1) / 26) + 64)).ToString() + (char)(((colId - 1) % 26) + 65);
                //}

                // This logic would only work for Single, Double, Tripple, Quadruple...... Character Alphabets
                if (colId >= 1)
                {
                    do
                    {
                        s_colValue = ((char)(65 + ((colId - 1) % 26))).ToString() + s_colValue;
                        colId = (colId - ((colId - 1) % 26)) / 26;
                    } while (colId > 0);
                }
            }
            catch
            {

            }

            return s_colValue;
        }

        public static string GetRangeName(int noOfColumns, int noOfRows)
        {
            return ExcelHelper.GetColumnNameFromIndex(1) + "1:" + ExcelHelper.GetColumnNameFromIndex(noOfColumns) + noOfRows.ToString();
        }

        public void DataTableToExcel(int startRowId, int startColId, System.Data.DataTable cellValues)
        {
            try
            {
                int n_rowCnt = startRowId;
                int n_colCnt = startColId;

                // Writing Column Headings
                foreach (DataColumn dcol in cellValues.Columns)
                {
                    WriteToCell(n_rowCnt, n_colCnt, dcol.ColumnName.ToString());
                    n_colCnt++;
                }

                n_rowCnt++;
                n_colCnt = startColId;

                // Writing Row Data
                foreach (DataRow drows in cellValues.Rows)
                {
                    for (int n_loop = 0; n_loop <= cellValues.Columns.Count - 1; n_loop++)
                    {
                        if (drows[n_loop] == System.DBNull.Value)
                        {
                            WriteToCell(n_rowCnt, n_colCnt + (n_loop), String.Empty);
                        }
                        else
                        {
                            WriteToCell(n_rowCnt, n_colCnt + (n_loop), drows[n_loop].ToString());
                        }
                    }
                    ++n_rowCnt;
                }
            }
            catch
            {

            }
        }

        public System.Data.DataTable ExcelToDataTable(int headingsRowId, object startLoc, object endLoc)
        {
            System.Data.DataTable returnTable = new DataTable();

            for (int ihy = 1; ihy <= TotalNoOfColumns; ihy++)
            {
                returnTable.Columns.AddRange(new DataColumn[] { new DataColumn(ReadFromCellString(headingsRowId, ihy), typeof(string)) });
            }

            try
            {
                Object[,] data = ReadFromCells(startLoc, endLoc);

                for (int loopRowCount = 1; loopRowCount <= TotalNoOfRows; loopRowCount++)
                {


                    DataRow texRow = returnTable.NewRow();
                    for (int loopColumnCount = 1; loopColumnCount <= data.GetLength(1); loopColumnCount++)
                    {
                        texRow[loopColumnCount - 1] = data.GetValue(loopRowCount, loopColumnCount).ToString();
                    }
                    returnTable.Rows.Add(texRow);
                }

                return returnTable;
            }
            catch
            {
                return returnTable;
            }
        }

        public string ConvertInteger2Alphabet(int colId)
        {
            string s_colValue = null;

            try
            {
                // This logic would only work for only for Single Character Alphabets
                //if (colId >= 1 && colId <= 26)
                //{
                //    s_colValue = ((char)((colId + 65) - 1)).ToString();
                //}

                // This logic would only work for only Double Character Alphabets 
                //if (colId >= 27 && colId <= 256)
                //{
                //    s_colValue = ((char)(((colId - 1) / 26) + 64)).ToString() + (char)(((colId - 1) % 26) + 65);
                //}

                // This logic would only work for Single, Double, Tripple, Quadruple...... Character Alphabets
                if (colId >= 1)
                {
                    do
                    {
                        s_colValue = ((char)(65 + ((colId - 1) % 26))).ToString() + s_colValue;
                        colId = (colId - ((colId - 1) % 26)) / 26;
                    } while (colId > 0);
                }
            }
            catch
            {

            }

            return s_colValue;
        }

        public static string GetNewTempPath()
        {
            string tableName = "StudentImport";
            string filepath = Properties.Settings.Default.DBBackupDirectoryPath + tableName + ".xls";
            if (System.IO.File.Exists(filepath))
            {
                filepath = Properties.Settings.Default.DBBackupDirectoryPath + tableName + "." +
                        new Random().Next(1000, 100000).ToString() + ".xls";
                while (System.IO.File.Exists(filepath))
                {
                    filepath = Properties.Settings.Default.DBBackupDirectoryPath + tableName + "." +
                        new Random().Next(1000, 100000).ToString() + ".xls";
                }
            }
            return filepath;
        }

        private void WriteTemplateHeadings(List<string> columns)
        {
            for (int col = 1; col <= columns.Count; col++)
            {
                XlWorkSheetObj.Cells[1, col] = columns[col - 1];
            }
        }
        private void Set_BG_Font_ColWidth_Comment(List<string> columns)
        {
            object misValue = System.Reflection.Missing.Value;
            XlWorkSheetObj.Range[GetRangeName(columns.Count, 1)].Font.Bold = true;
            for (int c = 1; c <= columns.Count; c++)
            {
                ((Excel.Range)XlWorkSheetObj.Columns[c]).ColumnWidth = 20;
            }

            XlWorkSheetObj.Name = "Import Template";
            Excel.Range nm = XlWorkSheetObj.get_Range("A1",
                ExcelHelper.GetColumnNameFromIndex(columns.Count) + "1");

            nm.Interior.ColorIndex = 44;
            XlWorkSheetObj.get_Range("A1", misValue).AddComment("DO NOT EDIT OUTSIDE OF THE COLORED RANGE.").Visible = true;
        }

    }
}
